var db = openDatabase('syncedhistory3', '1.0', 'my first database', 10 * 1024 * 1024);
//TODO: try catch everyting
//TODO: if(!undefined) callback everywhere
//TODO: change last_row shit for adding devices too, and because of that, cahnge the recursive method in background maybe too 
function createTables(callback) {
	db.transaction(function(tx) {
		tx.executeSql('CREATE TABLE IF NOT EXISTS visit (id INTEGER PRIMARY KEY, link TEXT, title TEXT, device_id INTEGER, FOREIGN KEY(device_id) REFERENCES device(id));', [], function(tx, results) {
			tx.executeSql('CREATE TABLE IF NOT EXISTS visit2date (visit_id INTEGER, date DATE, status INTEGER, FOREIGN KEY(visit_id) REFERENCES visit(id));', [], function(tx, results) {
				tx.executeSql('CREATE TABLE IF NOT EXISTS device (id INTEGER PRIMARY KEY, device_name TEXT, UNIQUE (device_name));', [], function(tx, results) {
					if(typeof(callback) == "function"){
						callback();
					}
				});
			});
		});
	});
}

function dropTables(callback) {
	db.transaction(function(tx) {
		tx.executeSql('DROP TABLE visit;', [], function(tx, results) {
			tx.executeSql('DROP TABLE visit2date;', [], function(tx, results) {
				tx.executeSql('DROP TABLE device;', [], function(tx, results) {
					if(typeof(callback) == "function"){
						callback();
					}
				});
			});
		});
	});
}

function readNewVisits(date_begin,date_end,dev_id,callback) {	
	db.transaction(function(tx) {
		tx.executeSql('SELECT visit.*, visit2date.*, device.device_name FROM (visit INNER JOIN visit2date ON visit.id=visit2date.visit_id) INNER JOIN device ON visit.device_id = device.id where visit2date.status=1 and visit.device_id=? and date between ? and ? ORDER BY date ASC;', [ dev_id, date_begin, date_end ], function(tx, results) {
			var len = results.rows.length, i;
			var msgBoard = [];

			for (i = 0; i < len; i++) {
				msgBoard.push(new Array(results.rows.item(i).title,results.rows.item(i).link,results.rows.item(i).date));
			}
			callback(msgBoard);
		});
	});
}

function readVisits(keyword, date_begin, date_end, orderby, limit,offset,callback) {
	keyword="%"+keyword+"%"
	db.transaction(function(tx) {
		tx.executeSql('SELECT visit.*, visit2date.*, device.device_name FROM (visit INNER JOIN visit2date ON visit.id=visit2date.visit_id) INNER JOIN device ON visit.device_id = device.id where visit2date.status=1 and (link like ? or title like ?) and date between ? and ? ORDER BY ' + orderby + ' LIMIT ? OFFSET ?;', [keyword,keyword, date_begin, date_end,limit,offset ], function(tx, results) {
			var len = results.rows.length, i;
			var msgBoard = [];

			for (i = 0; i < len; i++) {
				msgBoard.push({
					id : results.rows.item(i).id,
					link : results.rows.item(i).link,
					title : results.rows.item(i).title,
					device : results.rows.item(i).device_name,
					date : results.rows.item(i).date
				});
			}
			callback(msgBoard);
		});
	});
}

function readAllVisits(callback) {
	db.transaction(function(tx) {
		tx.executeSql('SELECT visit.*, visit2date.*, device.device_name FROM (visit INNER JOIN visit2date ON visit.id=visit2date.visit_id) INNER JOIN device ON visit.device_id = device.id where visit2date.status=1', [], function(tx, results) {
			var len = results.rows.length, i;
			var msgBoard = [];
			for (i = 0; i < len; i++) {
				msgBoard.push({
					id : results.rows.item(i).id,
					link : results.rows.item(i).link,
					title : results.rows.item(i).title,
					device : results.rows.item(i).device_name,
					date : results.rows.item(i).date
				});
			}
			callback(msgBoard);
		});
	});
}

function readAllDeletedVisits(callback) {
	db.transaction(function(tx) {
		tx.executeSql('SELECT visit2date.date, device.device_name FROM (visit INNER JOIN visit2date ON visit.id=visit2date.visit_id) INNER JOIN device ON visit.device_id = device.id where visit2date.status=0', [], function(tx, results) {
			var len = results.rows.length, i;
			var msgBoard = [];
			for (i = 0; i < len; i++) {
				msgBoard.push(new Array(					
					results.rows.item(i).device_name,
					results.rows.item(i).date
				));
			}
			callback(msgBoard);
		});
	});
}

function getVisitID(link, title, device_id, callback) {
	db.transaction(function(tx) {
		tx.executeSql('SELECT id FROM visit where (link = ?) AND (device_id = ?) AND (title = ?)', [ link, device_id, title ], function(tx, results) {
			if (results.rows.length > 0) {
				callback(results.rows.item(0).id);
			} else {
				callback(0);
			}
		});
	});
}

function getDeviceID(devname, callback) {
	db.transaction(function(tx) {
		tx.executeSql('SELECT id FROM device where (device_name = ?) ', [devname], function(tx, results) {
			if (results.rows.length > 0) {
				callback(results.rows.item(0).id);
			} else {
				callback(0);
			}
		});
	});
}
function getVisitIDWithDateAndDevice(device_name,date, callback) {
	getDeviceID(device_name,function(device_id){
		getVisitIDWithDateAndDeviceID(device_id,date, callback);		
	});
}

function getVisitIDWithDateAndDeviceID(device_id,date, callback) {
	db.transaction(function(tx) {
		tx.executeSql('SELECT visit.id FROM visit INNER JOIN visit2date ON visit.id=visit2date.visit_id where (date = ?) AND (device_id = ?) ', [ date, device_id ], function(tx, results) {
			if (results.rows.length > 0) {
				callback(results.rows.item(0).id);
			} else {
				callback(0);
			}
		});
	});
}
function getVisitDateCount(visit_id, callback) {
	db.transaction(function(tx) {
		tx.executeSql('SELECT COUNT(*) AS c FROM visit2date where (visit_id = ?) ', [ visit_id ], function(tx, results) {
			callback(results.rows.item(0).c);
		});
	});
}

function insertVisit(link, title, device_id, callback) {
	db.transaction(function(tx) {
		tx.executeSql('INSERT INTO visit (link, title, device_id) VALUES (?, ?, ?)', [ link, title, device_id ]);

		if(typeof(callback) == "function"){
			callback();
		}
	});
}

function insertvisit2date(visit_id, date, callback) {
	db.transaction(function(tx) {
		tx.executeSql('INSERT INTO visit2date (status,visit_id, date) VALUES (1,?, ?)', [ visit_id, date ]);

		if(typeof(callback) == "function"){
			callback();
		}
	});
}

function insertdevice(device_name, callback) {
	db.transaction(function(tx) {
		tx.executeSql('SELECT id FROM device where (device_name = ?)', [ device_name ], function(tx, results) {
		if(typeof(callback) == "function"){
			if (results.rows.length > 0) {
				callback(results.rows.item(0).id, false);
			} else {
				db.transaction(function(tx) {
					tx.executeSql('INSERT INTO device (device_name) VALUES (?)', [ device_name ]);
					
						db.transaction(function(tx) {
							tx.executeSql('SELECT last_insert_rowid() from device;', [], function(tx, results) {
								id = results.rows.item(0)['last_insert_rowid()'];
								callback(id, true);
							});
						});
					
				});
			}
		}
		});
	});
}

function deleteVisit(link, title, device_id, date, callback) {
	db.transaction(function(tx) {
		getVisitID(link, title, device_id, function(id1) {
			tx.executeSql('DELETE FROM visit2date where date=? and visit_id=?', [ date, id1 ]);
			getVisitDateCount(id1, function(count) {
				if (count != 0) {
					if(typeof(callback) == "function"){
						callback();
					}
				} else {
					db.transaction(function(tx) {
						tx.executeSql('DELETE FROM visit where id=?', [ id1 ]);
						if(typeof(callback) == "function"){
							callback();
						}
					});
				}

			});
		});
	});
}

function deleteAllMarkedVisits(callback) {
	
	readAllDeletedVisits(function(ddp_array) {
		deleteAllMarkedVisitsRec(ddp_array,0,callback);
	});	
	
}

function deleteAllMarkedVisitsRec(ddp_array,i,callback) {
	if(ddp_array.length==i){
		if(typeof(callback) == "function"){
							callback();
		}
		return;
	}	
	
	getVisitIDWithDateAndDevice(ddp_array[i][0],ddp_array[i][1],function(v_id){
		deleteVisitWithIDAndDate(v_id,ddp_array[i][1],function(){			
			i++;
			deleteAllMarkedVisitsRec(ddp_array,i,callback);
		});	
			
	});
}

function deleteVisitWithIDAndDate(visit_id, date, callback) {
	db.transaction(function(tx) {
		tx.executeSql('DELETE FROM visit2date where date=? and visit_id=?', [ date, visit_id ]);

		getVisitDateCount(visit_id, function(count) {
			if (count != 0) {

				if(typeof(callback) == "function"){
					callback();
				}

			} else {
				db.transaction(function(tx) {
					tx.executeSql('DELETE FROM visit where id=?', [ visit_id ]);
					if(typeof(callback) == "function"){
						callback();
					}
				});
			}

		});
	});
}


function markAsDeleted(visit_id, date, callback) {
	db.transaction(function(tx) {
		tx.executeSql('UPDATE visit2date SET status=0 where date=? and visit_id=?', [ date, visit_id ]);
			
			//delete link and title content for privacy - BEGIN
			db.transaction(function(tx) {
			tx.executeSql('SELECT COUNT(*) AS c FROM visit2date where (visit_id = ?) and status=1 ', [ visit_id ], function(tx, results) {
				var count=results.rows.item(0).c;
				
				if (count != 0) {

				if(typeof(callback) == "function"){
					callback();
				}

				} else {
				db.transaction(function(tx) {
					tx.executeSql('UPDATE visit SET link="" and title="" where visit_id=?', [ visit_id ]);
					if(typeof(callback) == "function"){
						callback();
					}
				});
				}				
				
			});
			});	
			//delete link and title content for privacy - END		
	});
}

function addVisit(link, title, device_id, date, callback) {
	getVisitID(link, title, device_id, function(id) {
		if (id == 0) {
			insertVisit(link, title, device_id, function() {				
				getVisitID(link, title, device_id, function(id2) {					
					insertvisit2date(id2, date, function() {
							if(typeof(callback) == "function"){
								callback(id2, true);
							}
					});
				});				
			});
		} else {
			db.transaction(function(tx) {
				tx.executeSql('SELECT visit_id from visit2date where (visit_id=?) AND (date = ?);', [ id, date ], function(tx, results) {
					if (results.rows.length == 0) {
						insertvisit2date(id, date, function() {
							if(typeof(callback) == "function"){
								callback(id, true);
							}
						});
					} else {
						if(typeof(callback) == "function"){
							callback(id, false);
						}
					}
				});
			});
		}
	});
}

function getMaxDate(callback){
	db.transaction(function(tx) {
		tx.executeSql('SELECT MAX(date) AS max FROM visit2date;', [ ], function(tx, results) {
			if (results.rows.length > 0) {
				callback(results.rows.item(0).max);
			} else {
				callback(0);
			}
		});
	});
}

function getUniqueVisitCount(callback){
	db.transaction(function(tx) {
		tx.executeSql('SELECT count(*) as count FROM (visit INNER JOIN visit2date ON visit.id=visit2date.visit_id) INNER JOIN device ON visit.device_id = device.id where visit2date.status=1', [], function(tx, results) {
			if (results.rows.length > 0) {
				callback(results.rows.item(0).count);
			} else {
				callback(-1);
			}
		});
	});
}


function getVisitCount(callback){
	db.transaction(function(tx) {
		tx.executeSql('SELECT count(*) as count FROM visit;', [], function(tx, results) {
			if (results.rows.length > 0) {
				callback(results.rows.item(0).count);
			} else {
				callback(-1);
			}
		});
	});
}

function getVisit2DateCount(callback){
	db.transaction(function(tx) {
		tx.executeSql('SELECT count(*) as count FROM visit2date;', [], function(tx, results) {
			if (results.rows.length > 0) {
				callback(results.rows.item(0).count);
			} else {
				callback(-1);
			}
		});
	});
}

createTables();